<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>The SELECT statement in SQLite</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="SQLite, SQL, SELECT, database">
<meta name="description" content="In this part of the SQLite tutorial, we will
cover the SELECT statement.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>The SELECT statement</h1>


<p>
This part of the SQLite tutorial will be covering the SELECT statement understood by the SQLite in detail.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>


<h2>Retrieving all data</h2>

<p>
The following SQL statement is one of the most common ones. It is also one of the most
expensive ones. 
</p>


<pre class="code">
sqlite> SELECT * FROM Cars;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600  
</pre>

<p>
Here we retrieve all data from the Cars table. 
</p>


<h3>Selecting specific columns</h3>

<p>
We can use the SELECT statement to retrieve specific columns. 
The column names follow the SELECT word. 
</p>

<pre class="code">
sqlite> SELECT Name, Cost FROM Cars;
Name        Cost      
----------  ----------
Audi        52642     
Mercedes    57127     
Skoda       9000      
Volvo       29000     
Bentley     350000    
Citroen     21000     
Hummer      41400     
Volkswagen  21600     
</pre>

<p>
We retrieve the Name and the Cost columns. The column names are separated by commas. 
</p>


<h3>Renaming column names</h3>

<p>
We can rename the column names of the returned result set. For this, we use the
AS clause. 
</p>

<pre class="code">
sqlite> SELECT Name, Cost AS Price FROM Cars;
Name        Price     
----------  ----------
Audi        52642     
Mercedes    57127     
Skoda       9000      
Volvo       29000     
Bentley     350000    
Citroen     21000     
Hummer      41400     
Volkswagen  21600  
</pre>

<p>
Say we wanted to name the column Price rather than Cost. With the above SQL statement, we
have accomplished this. 
</p>


<h2>Limiting data output</h2>

<p>
As we mentioned above, retrieving all data is expensive when dealing with large amounts of data. 
We can use the LIMIT clause to limit the data amount returned by the statement. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars LIMIT 4;
Id          Name        Cost      
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
</pre>

<p>
The LIMIT clause limits the number of rows returned to 4. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars LIMIT 2, 4;
Id          Name        Cost      
----------  ----------  ----------
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000  
</pre>

<p>
This statement selects four rows skipping the first two rows. 
</p>

<p>
The OFFSET clause following LIMIT specifies how many rows to skip 
at the beginning of the result set. This is an alternate solution
to the previous one. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars LIMIT 4 OFFSET 2;
Id          Name        Cost      
----------  ----------  ----------
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000    
</pre>

<p>
Here we select all data from max four rows, and we begin with the third row. 
The OFFSET clause skips the first two rows. 
</p>


<h2>Ordering data</h2>

<p>
We use the ORDER BY clause to sort the returned data set. The ORDER BY clause 
is followed by the column on which we do the sorting. The ASC keyword sorts 
the data in ascending order, the DESC in descending order. 
</p>

<pre class="code">
sqlite> SELECT * FROM Cars ORDER BY Cost;
Id          Name        Cost      
----------  ----------  ----------
3           Skoda       9000      
6           Citroen     21000     
8           Volkswagen  21600     
4           Volvo       29000     
7           Hummer      41400     
1           Audi        52642     
2           Mercedes    57127     
5           Bentley     350000 
</pre>

<p>
The default sorting is in ascending order. The ASC clause can be omitted.
</p>

<pre class="code">
sqlite> SELECT Name, Cost FROM Cars ORDER BY Cost DESC;
Name        Cost      
----------  ----------
Bentley     350000    
Mercedes    57127     
Audi        52642     
Hummer      41400     
Volvo       29000     
Volkswagen  21600     
Citroen     21000     
Skoda       9000   
</pre>

<p>
In the above SQL statement, we select Name, Cost columns from the Cars table and sort 
it by the Cost of the cars in descending order. So the most expensive cars come first. 
</p>


<h2>Selecting specific rows with the WHERE Clause</h2>

<p>
In the following examples, we are going to use the Orders table. 
</p>

<pre class="code">
sqlite> SELECT * FROM Orders;
Id          OrderPrice  Customer  
----------  ----------  ----------
1           1200        Williamson
2           200         Robertson 
3           40          Robertson 
4           1640        Smith     
5           100         Robertson 
6           50          Williamson
7           150         Smith     
8           250         Smith     
9           840         Brown     
10          440         Black     
11          20          Brown  
</pre>

<p>
Here we see all the data from the Orders table. 
</p>

<p>
Next, we want to select a specific row.
</p>

<pre class="code">
sqlite> SELECT * FROM Orders WHERE id=6;
Id          OrderPrice  Customer  
----------  ----------  ----------
6           50          Williamson
</pre>

<p>
The above SQL statement selects a row which has id 6.
</p>

<pre class="code">
sqlite> SELECT * FROM Orders WHERE Customer="Smith";
Id          OrderPrice  Customer  
----------  ----------  ----------
4           1640        Smith     
7           150         Smith     
8           250         Smith    
</pre>

<p>
The above SQL statement selects all orders from the Smith customer. 
</p>

<p>
We can use the LIKE keyword to look for a specific pattern in the data.
</p>

<pre class="code">
sqlite> SELECT * FROM Orders WHERE Customer LIKE 'B%';
Id          OrderPrice  Customer  
----------  ----------  ----------
9           840         Brown     
10          440         Black     
11          20          Brown   
</pre>

<p>
This SQL statemet selects all orders from customers whose names begin
with B character. 
</p>


<h2>Removing duplicate items</h2>

<p>
The DISTINCT keyword is used to select only unique items
from the result set. 
</p>

<pre class="code">
sqlite> SELECT Customer FROM Orders WHERE Customer LIKE 'B%';
Customer  
----------
Brown     
Black     
Brown  
</pre>

<p>
This time we have selected customers whose names begin with B character.
We can see, that Brown is mentioned twice. To remove duplicates, we use the
DISTINCT keyword.
</p>

<pre class="code">
sqlite> SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%';
Customer  
----------
Black     
Brown   
</pre>

<p>
This is the correct solution. 
</p>


<h2>Grouping data</h2>

<p>
The GROUP BY clause is used to combine database records with identical values 
into a single record. It is often used with the aggregation functions.
</p>

<p>
Say we wanted to find out the sum of each customers' orders. 
</p>

<pre class="code">
sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer;
Total       Customer  
----------  ----------
440         Black     
860         Brown     
340         Robertson 
2040        Smith     
1250        Williamson
</pre>
 
<p>
The <code>sum()</code> function returns the total sum of a numeric column. The GROUP BY clause 
divides the total sum among the customers. So we can see that Black has ordered 
items for 440 or Smith for 2040.
</p>

<p>
We cannot use the WHERE clause when aggregate functions are used. We use the 
HAVING clause instead.
</p>

<pre class="code">
sqlite> SELECT sum(OrderPrice) AS Total, Customer FROM Orders 
        GROUP BY Customer HAVING sum(OrderPrice)>1000;
Total       Customer  
----------  ----------
2040        Smith     
1250        Williamson
</pre>
 
<p>
The above SQL statement selects customers whose total orders where greater than 1000 units. 
</p>

<p>
In this part of the SQLite tutorial, we mentioned the SQL SELECT statement in more detail. 
</p>

<div class="center"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* horizontal */
google_ad_slot = "1734478269";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 
<br>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified January 8, 2013  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body> 
</html>
